-- create database payir_db;
-- \c payir_db;

begin;
create schema payir_schema;
set search_path=payir_schema;
create sequence crop_id_seq start 1;
create table crop
	(
	crop_id INTEGER Not Null default nextval('crop_id_seq'), --Id Column for the table
	crop_name VARCHAR Not Null, --	Name of the crop
	description VARCHAR Null, --Any specific remarks like details
	crop_type VARCHAR Null, --Just to classify the crop into some type
	crop_yield numeric Null, --Yield per unit hectare of land
	crop_source VARCHAR Null, --Source where this crop can be acquired
	crop_season VARCHAR Null, --Good season for the crop to be grown
	crop_harvest_days integer, --default time for the crop to mature	
	constraint crop_id_pk primary key (crop_id),
	constraint crop_name_unq unique (crop_name)
	);	 
create table crop_lang
	(
	crop_id integer not null,
	language_id char(4) not null, --tain, hiin, mlin etc
	crop_name varchar not null,
	description varchar null,
	crop_type varchar null, 
	crop_source varchar null,
	crop_season varchar null,
	constraint crop_id_fk foreign key (crop_id) references crop,
	constraint crop_language_unq unique (crop_id, language_id)
	);

create sequence land_id_seq start 1;
create table land
	(
	land_id integer Not NULL default nextval('land_id_seq'), -- 	Index field for Land data
	land_reg_number VARCHAR NOT NULL, --	Registration Number or other unique identification of the land
	land_name varchar not null, -- identification name given by the farmer
	land_address varchar not null, -- address of the land
	land_state char(2) Not Null, --	State where this land is located tn, kl, kn, ap, up etc
	land_village VARCHAR Not Null, --Village/town where the land is located
	land_city VARCHAR Not Null, -- Nearest city where land is located
	land_size numeric Not Null, --Size of the land 
	uom char(4) Not Null, --Unit of measurement for this land size sqft, acre etc
	land_type varchar null, --classification of the land
	constraint land_id_pk primary key (land_id),
	constraint land_reg_number_unq unique (land_reg_number)
	);

create sequence farmer_id_seq start 1;
create table farmer
	(
	farmer_id integer not null default nextval('farmer_id_seq'), --Unique id for Farmer
	farmer_name 	VARCHAR	NOT NULL, --	Name of the Farmer
	address1 	VARCHAR NOT NULL, --	Address field 1
	address2 	VARCHAR Null, --Address field 2
	address3 	VARCHAR	Null, --Address field 3
	city 		VARCHAR Not Null, --Nearest City where farmer belongs to 
	village 	VARCHAR Not Null, --Village or town where the farmer resides
	state 		char(2)	Not Null, --State where farmer resides
	dob 		DATE Null, --Date of birth of the farmer
	nominee 	VARCHAR Null, --Nearest kin or nominee name for this farmer
	language 	char(4) Null, --Language in which this record is stored
	constraint farmer_id_pk primary key (farmer_id)
	);
create sequence farmer_land_id_seq start 1;
create table farmer_land_relation
	(
	farmer_land_id 	integer not null default nextval('farmer_land_id_seq'), --Unique id for this relationship
	farmer_id 	integer	Not Null,
	land_id 	integer	Not Null,
	constraint farmer_land_relation_pk primary key (farmer_land_id),
	constraint farmer_id_fk foreign key (farmer_id) references farmer,
	constraint land_id_fk foreign key (land_id) references land
	);

create table yearly_produce --Season, Crop, Production, Sale, Sale Price (at the end of each season)
	(
	produce_id Integer,--	Unique id of the record
	produce_year integer NOT NULL, -- 	Year of the produce
	season 	VARCHAR Null, --	Season of this produce
	crop_id integer	Not Null, --Reference from Crop table
	land_farmer_id integer Null, --Reference from farmer_land_relation table
	production_quantity numeric Not Null, --Volume of Production
	sales_quantity 	numeric Null, --Volume of sales 
	production_cost numeric Null, --Total Cost of the crop and irrigation
	sales_price 	numeric Null, --Average sales price of crop per unit crop
	constraint yearly_produce_pk primary key (produce_id),
	constraint year_season_crop_land_farmer_unq unique (produce_year, season, crop_id, land_farmer_id),
	constraint crop_id_fk foreign key (crop_id) references crop,
	constraint land_farmer_id foreign key (land_farmer_id) references farmer_land_relation
	);
commit;

begin;
create table login_details
	(
	login_name varchar,
	password varchar,
	farmer_id integer,
	constraint farmer_id_fk foreign key (farmer_id) references farmer,
	constraint login_name_unq unique (login_name),
	constraint farmer_id_unq unique (farmer_id)
	);
commit;
